package com.rybbaby.tss.front.tssStatistics.dao.imp;

import com.rybbaby.tss.core.bean.PageBean;
import com.rybbaby.tss.core.utils.BeanUtils;
import com.rybbaby.tss.core.web.dao.imp.GenericDao;
import com.rybbaby.tss.front.tssStatistics.dao.IViewTransitionSurplusAttendanceDayDao;
import com.rybbaby.tss.front.tssStatistics.entity.ViewTransitionSurplusAttendanceDay;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @description 员工表数据库操作实现
 * 
 * @author 雷春琼
 * 
 * @createDate 2017-08-10 上午12:25:24
 */
@Repository
public class ViewTransitionSurplusAttendanceDayDaoImp extends GenericDao<ViewTransitionSurplusAttendanceDay> implements IViewTransitionSurplusAttendanceDayDao {


    @Override
    public PageBean pageList(HashMap<String, Object> paramHashMap, int pageNo, int pageSize) throws InstantiationException, IllegalAccessException {


        String id = (String)paramHashMap.get("id");
        String adminUserId = (String)paramHashMap.get("adminUserId");
        String type = (String)paramHashMap.get("type");
        String nickName = (String)paramHashMap.get("nickName");
        String contactPhone = (String)paramHashMap.get("contactPhone");
        String birthdayDate_begin = (String)paramHashMap.get("birthdayDate_begin");
        String birthdayDate_end = (String)paramHashMap.get("birthdayDate_end");
        String surplusClassHours_begin = (String)paramHashMap.get("surplusClassHours_begin");
        String surplusClassHours_end = (String)paramHashMap.get("surplusClassHours_end");
        String surplusPresentClassHours_begin = (String)paramHashMap.get("surplusPresentClassHours_begin");
        String surplusPresentClassHours_end = (String)paramHashMap.get("surplusPresentClassHours_end");
        String balances_begin = (String)paramHashMap.get("balances_begin");
        String balances_end = (String)paramHashMap.get("balances_end");
        String endTime_end = (String)paramHashMap.get("endTime_end");

        String idSql = "";
        String adminUserIdSql = "";
        String nickNameSql = "";
        String contactPhoneSql = "";
        String birthdayDate_beginSql = "";
        String birthdayDate_endSql = "";
        String surplusClassHours_beginSql = "";
        String surplusClassHours_endSql = "";
        String surplusPresentClassHours_beginSql = "";
        String surplusPresentClassHours_endSql = "";
        String balances_beginSql = "";
        String balances_endSql = "";
        String endTime_endSql = "SELECT\n" +
                "\t\t*\n" +
                "\tFROM\n" +
                "\t\ttss_member_package\n" +
                "\tWHERE\n" +
                "\t\ttss_member_package.type = "+type+"";




        if(id!=null && !id.equals("")){
            idSql = "\tAND tss_student.id = '"+id+"'\n";
        }
        if(adminUserId!=null && !adminUserId.equals("")){
            adminUserIdSql = "\tAND tss_student.adminUserId in ('"+adminUserId+"')\n";
        }
        if(nickName!=null && !nickName.equals("")){
            nickNameSql = "\tAND tss_student.nickName LIKE '%"+nickName+"%'\n";
        }
        if(contactPhone!=null && !contactPhone.equals("")){
            contactPhoneSql = "\tAND tss_student.contactPhone LIKE '%"+contactPhone+"%'\n";
        }
        if(birthdayDate_begin!=null && !birthdayDate_begin.equals("")){
            birthdayDate_beginSql =  "\tAND unix_timestamp(tss_student.birthdayDate) >= unix_timestamp('"+birthdayDate_begin+"')\n";
        }
        if(birthdayDate_end!=null && !birthdayDate_end.equals("")){
            birthdayDate_endSql = "\tAND unix_timestamp(tss_student.birthdayDate) <= unix_timestamp('"+birthdayDate_end+"')\n";
        }
        if(surplusClassHours_begin!=null && !surplusClassHours_begin.equals("")){
            surplusClassHours_beginSql = "AND surplusAttendanceDays >"+surplusClassHours_begin+"\n";
        }
        if(surplusClassHours_end!=null && !surplusClassHours_end.equals("")){
            surplusClassHours_endSql = "AND surplusAttendanceDays <"+surplusClassHours_end+"\n";
        }
        if(surplusPresentClassHours_begin!=null && !surplusPresentClassHours_begin.equals("")){
            surplusPresentClassHours_beginSql = "AND surplusPresentAttendanceDays >"+surplusPresentClassHours_begin+"\n";
        }
        if(surplusPresentClassHours_end!=null && !surplusPresentClassHours_end.equals("")){
            surplusPresentClassHours_endSql = "AND surplusPresentAttendanceDays <"+surplusPresentClassHours_end+"\n";
        }
        if(balances_begin!=null && !balances_begin.equals("")){
            balances_beginSql = "AND balances >"+balances_begin+"\n";
        }
        if(balances_end!=null && !balances_end.equals("")){
            balances_endSql = "AND balances <"+balances_end+"\n";
        }
        if(endTime_end!=null && !endTime_end.equals("")){
            endTime_endSql =    "\tSELECT\n" +
                                "\t\tF.*\n" +
                                "\tFROM\n" +
                                "\t\ttss_member_package\n" +
                                "\tLEFT JOIN(\t\n" +
                                "\t\tSELECT\n" +
                                "\t\t\t*\n" +
                                "\t\tFROM\n" +
                                "\t\t\ttss_member_package_history\n" +
                                "\t\tWHERE\n" +
                                "\t\t\t1=1\n" +
                                "\t\tAND unix_timestamp(\n" +
                                "\t\t\ttss_member_package_history.updateTime\n" +
                                "\t\t) <= unix_timestamp('"+endTime_end+"')\n" +
                                "\t)F\n" +
                                "\tON F.memberPackageId = tss_member_package.id\n" +
                                "\tWHERE\n" +
                                "\t\ttss_member_package.type = "+type+"\n" +
                                "\t\n" ;
        }

        String sql = "SELECT \n" +
                "\tD.* \n" +
                "FROM(\n" +
                "SELECT \n" +
                "\tC.id,\n" +
                "\tC.`name` AS studentName,\n" +
                "\tC.birthdayDate,\n" +
                "\tC.contactPhone,\n" +
                "\tIFNULL(SUM(B.attendanceDay),0) AS attendanceDays,\n" +
                "\tIFNULL(SUM(B.giftAttendanceDay),0) AS giftAttendanceDays,\n" +
                "\tIFNULL(SUM(B.consumptionAttendanceDay),0) AS consumptionAttendanceDays,\n" +
                "\tIFNULL(SUM(B.absentDay),0) AS absentDays,\n" +
                "\tIFNULL(SUM(B.surplusAttendanceDay),0) AS surplusAttendanceDays,\n" +
                "\tIFNULL(SUM(B.surplusPresentAttendanceDay),0) AS surplusPresentAttendanceDays,\n" +
                "\tIFNULL(SUM(B.balance),0) AS balances,\n" +
                "\tIFNULL(tss_meal_fee.surplusAmout,0) AS mealFeeAmout\n" +
                "FROM\n" +
                "(\n" +
                "\tSELECT\n" +
                "\t\t*\n" +
                "\tFROM\n" +
                "\t\ttss_student\n" +
                "\tWHERE\n" +
                "\t\tisMember = 1\n" +
                adminUserIdSql +
                idSql +
                nickNameSql +
                contactPhoneSql +
                birthdayDate_beginSql +
                birthdayDate_endSql +
                ")C\n" +
                "LEFT JOIN\n" +
                "(\n" +
                "SELECT\n" +
                "\tA.*,\n" +
                "\ttss_package.totalClassHour\n" +
                "FROM(\n" +
                "SELECT\n" +
                "\tE.packageId,\n" +
                "\tE.memberId,\n" +
                "\tE.type,\n" +
                "\n" +
                "\tE.attendanceDay,\n" +
                "\tE.giftAttendanceDay,\n" +
                "\tE.consumptionAttendanceDay,\n" +
                "\tE.absentDay,\n" +
                "\tE.surplusAttendanceDay,\n" +
                "\tE.surplusPresentAttendanceDay,\n" +
                "\t\n" +
                "\tE.nowUnitPrice,\n" +
                "\tSUM(E.surplusFormalAttendanceDay+E.positiveUnusedAttendanceDay) AS positiveLesson,\n" +
                "\tE.nowUnitPrice*SUM(E.surplusFormalAttendanceDay+E.positiveUnusedAttendanceDay) AS balance\n" +
                "FROM(\n" +
                endTime_endSql +
                ")E\n" +
                "GROUP BY E.id\n" +
                ")A \n" +
                "LEFT JOIN tss_package\n" +
                "ON A.packageId = tss_package.id\n" +
                ")B\n" +
                "ON B.memberId = C.id\n" +
                "LEFT JOIN tss_meal_fee\n" +
                "ON tss_meal_fee.stuId = C.id\n" +
                "WHERE B.type = "+type+"\n" +
                "GROUP BY C.id\n" +
                ")D\n" +
                "WHERE 1=1\n" +
                surplusClassHours_beginSql +
                surplusClassHours_endSql +
                surplusPresentClassHours_beginSql +
                surplusPresentClassHours_endSql +
                balances_beginSql +
                balances_endSql;
        System.out.println(sql);
        PageBean pageBean = this.pageList(sql, pageNo, pageSize);
        List<ViewTransitionSurplusAttendanceDay> populate = BeanUtils.populate(ViewTransitionSurplusAttendanceDay.class, (List<Map<String, Object>>) pageBean.getList(), "attendanceRate");
        pageBean.setList(populate);
        return pageBean;
    }

}
